装修注意项

施工规范 营业执照 资质证书 能否出具合格的票据

建议把此条款改为对装修总款额的的约定,比如约定业主不能减项以使整个工程费用低于多少钱

应详细注明施工工期

验收方式可在合同上约定由政府质检站来验收,这样你就可以不为请质检站而另付费用。

合同要由法定代表人签订,如有委托代理人的,需要复印委托书,同时向装饰公司索要工商执照的复印件和资质证明的复印件,这两个复印件都应该加盖公司章,还要索要项目经理和工程负责人的身份证复印件或公司正式职工的工作证复印件以及联系电话

交付的工程款要由业主亲自交到公司财务,并索要建筑安装专用发票,尽量防止出现其他人代收工程款的情况

工程完工后不同的项目有不同的保修期,客户可依具体情况与公司商议,保留原证件或扣留部分工程款作为装修的质量保证金

装修公司包料的,要向其索要购买的材料明细表、合格证、发票

在装修工程竣工后,空气质量应符合国家标准,检测不合格的,如属承包人的责任,承包人应返工,并承担相应损失

mysql优化器选错索引

这边介绍一下,在一个表有多个索引的情况下,mysql是如何选择索引的。

优化器的逻辑

优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗cpu资源越少。但是,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
系统在真正开始执行语句之前,并不能精确的知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录条数。这个统计信息就是索引的“分区度”。一个索引上不同的数值的个数称之为“基数(cardinality)”,基数越大,索引的分群越好。可以使用show index方法看到一个索引的基数。
mysql通过采样统计的方法,获取到索引的基数。采样统计的时候,innodb默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计的信息不回固定不变。当变更的数据行数超过1/M的时候,就会触发一次索引统计。
在mysql中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择
– on,表示统计信息会持久化存储。这时,默认的N是20,M是10。
– off,表示统计信息只存储在内存中。这时,默认的N是8,M是16。
由于是采样统计,所以不管是N是20还是8,这个基数都是不准的。
另外,索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断执行这个语句本身要扫描多少行。对于普通索引来说,优化器还需要计算回表的时间,而全表扫描则不需要考虑这个时间成本。但是一般来说,选错索引的主要原因还是在于mysql没有准确的判断扫描行数。使用analyze table t命令可以重新统计索引信息。

索引选择异常和处理

1.可以使用force index强制使用一个索引。但是使用force index主要的问题还是变更的及时性。因为选错索引的情况比较少,所以开发的时候通常不会强制指定,而是等出了问题之后才会去修改。但是这样做对于开发和测试非常不友好,所以尽量把问题放在数据库内部解决。
2. 可以新建一个更适合的索引给优化器选择使用,或者删掉误用的索引。
3. 重新统计索引信息。

索引的选择

索引的选择

假设我们需要为一个全局唯一的列设置索引,这时候需要选择哪一种索引更合适?
首先了解下普通索引和唯一索引的查询机制
– 普通索引查询到满足条件的第一个记录后,需要查找下一个纪录,直到碰到第一个不满足条件的记录为止。
– 唯一索引只需要查询到满足的第一条记录后就会停止继续检索。
但是对于数据性能来说,两者的差距微乎其微。
因为innodb的数据是按照数据页为单位来读写的。也就是说读一条记录的时候会将这个数据页读到内存中去。
对于普通索引来讲,多做的那一次查找和判断操作只需要一次指针寻址和一次计算,其成本可以忽略不计。

更新的过程

当需要更新一个数据页的时候,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,innodb会将这些更新操作缓存在change buffer中,这样就不需要再从磁盘中读入这个数据页。
在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中雨这个页有关的操作,通过这种方式就能保证这个数据逻辑的正确性。
虽然名字叫change buffer,实际上他是一个持久化的数据,它在内存上的拷贝也会被写入到磁盘上。
将change buffer中的操作应用到原始数据页,得到更新结果的过程称之为merge。除了访问这个数据页会触发merge外,系统有后台线程会定期merge。在数据库正常关闭的过程中,也会执行merge。
change buffer可以减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用buffer pool的,所以这种方式还能够避免占用内存,提高内存利用率。
对于唯一索引来说,所有的更新操作都会先判断这个操作是否违反唯一性约束。因此,唯一索引的更新不能使用change buffer,只有普通索引才可以使用。
change buffer用的是buffer pool里的内存,因此不能无限增大。change buffer的大小可以通过参数innodb_change_buffer_max_size来设置,参数值代表着其可占用buffer pool的百分比。
因此,大量的更新操作的字段采用唯一索引会导致数据库产生大量的io操作,非常消耗系统资源。普通索引则可以通过change buffer来规避这个问题。

change buffer的使用场景

在merge的时候是真正进行数据更新的时候,而change buffer的主要目的就是讲记录的变更动作缓存下来,所以在一个数据页做merge之前,change buffer记录的变更越多,收益就越大。
因此,对于写多读少的业务来说,页面在写完后马上被访问到的概率很小,此时change buffer的使用效果最好。这种业务模型常见的就是账单、日志类。
反过来,如果一个业务的更新模式是写入后马上会做查询,那么即时写入了change buffer,但是由于马上要访问会立即触发merge,反而增加了change buffer的维护代价。所以这种业务外模式不适合使用change buffer。

结论

对于普通索引和唯一索引,两者在查询能力方面是没有太大差别,对于更新性能的影响,建议使用普通索引。
如果所有的更新操作后面会伴随着对这条记录的查询,应该要关闭change buffer。

事务隔离原理

首先说明下,begin/start transaction命令并不是一个事务的起点,在执行到他们之后的第一个操作innodb表的语句,事务才真正启动。
如果需要马上启动一个事务,可以使用start transaction with consistent snapshot这个命令。
只有真正意义上启动了事务,才会生成一个一致性视图。
在mysql中,有两个“视图”的概念:
1. view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果。创建视图的语法是create view ...,而它的查询方法和表一样。
2. innodb在实现MVCC时用到的一致性视读视图consistent read view,用于支持读提交和可重复读级别的隔离。
它没有物理结构,作用是事务执行的是时候来定义访问者可见的数据结构。

快照

在可重复读隔离下,事务在启动的时候就生成了快照。快照是基于整库的。
innodb里每个事务都有一个唯一的事务id(transaction id)。他在事务开始的时候向innodb的事务系统申请,按照申请的顺序严格递增。
而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把transaction id赋值给这个数据版本的id,记作row trx_id。同时,就得数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它。
也就是说,数据表中的一行记录,其实可能有多个版本,每个版本都有自己的row trx_id。
按照可重复读的定义,一个事务启动的时候,能够看到所有已提交的事务结果。但是之后,这个事务执行期间,其他事务的更新对它不可见。
因此,一个事务只需要在启动的时候设定一个数据版本,并且读数据的时候,只读其版本之前的数据。
在实现上,innodb为每个事务构造了一个数组,用来保存这个事务启动的瞬间,当前正在活动的所有事务ID。
数组里面的事务ID的最小值记为低水位,当前系统里已经创建过的事务ID的最大是加1记为高水位。
这个视图数组和高水位值,就组成了当前事务的一致性视图。而数据版本的可见性规则,就是基于数组row trx_id和这个一致性视图对比得到的结果。

更新数据都是先读后写的,而这个读只能读当前的值,称为当前读(current read)。

如何减小行锁对性能的影响

mysql的行锁是在引擎层由各个引擎自己实现的,但并不是所有的引擎都支持行锁。
比如MyISAM就不支持行锁。不支持行锁就意味着并发控制只能使用表锁,这就会影响并发处理的效率。

两阶段锁

在Innodb事务中,行锁是在需要的时候才加上的,在事务结束的时候才会去释放。这就是两阶段锁协议。
因此,如果你的事务中需要锁多个行,要把最可能赵成锁冲突、最有可能影响并发度的锁尽量往后放。

死锁和检测

当并发系统中不同线程出现循环资源依赖,涉及的线程都在相互等待别的线程释放资源时,就会导致这几个线程进入无限等待的状态,称之为死锁。
当出现死锁之后,我们有两种策略进行处理:
1. 直接进入等待状态,知道超时。超时时间可以通过参数innodb_lock_wait_timeout来设置
2. 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一事务,让其他事务得意继续执行。设置参数innodb_deadlock_detect设置为on,来开启这个功能。

在innodb中,innodb_lock_wait_timeout的默认值是50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过50s才会超时退出,其他线程才有可能继续执行。
对于这么长的等待时间,对业务来说往往是不能接受的,但是将等待时间设置过短,又会导致误伤正常的所等待。因此我们还是尽量采用第二种策略。
默认innodb_deadlock_detecton。主动死锁检测在发生死锁的时候是可以快速的发现并处理,但是它也会有额外的负担。一旦被死锁的行是热点行,对于时间复杂度是O(n)的死锁检测来说,将会小号大量的cpu资源。
因此我们就需要讨论如何解决由于这种热点行更新导致的性能问题。
一种方案是,如果能确保业务一定不会出现死锁,那就可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,一旦出现死锁就会带来大量的请求超时,这是业务有损的。
另一种方案是控制并发度。比如同一行同时最多只有10个线程在更新,那么死锁检测的成本就很低。但是一旦用户量比较大的时候,是无法保证并发数的。因此可以考虑在数据库服务端,采用中间件的形式操作,对于相同行的更新,进入引擎之前进入队列排队。
还有一种方案是通过业务设计的角度来优化这个问题。可以考虑通过将一行改成逻辑上多行来减少行锁的冲突,更新操作随机命中其中一行,这样就可以减少死锁检测的CPU消耗。

数据库锁设计的初衷是处理并发问题。当多用户共享资源产生并发访问的时候,数据库需要合理的控制资源的访问规则。锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围,mysql里面的锁可以分为全局锁、表级锁、行锁三类。

全局锁

全局锁是对整个数据库实例加锁。命令是Flush table with read lock(FTWRL)。
使用该命令后,数据更新语句(增删改,DML)、数据定义语句(建表、修改表结构,DDL)、更新的事务提交语句将全部被阻塞。
全局锁的典型使用场景是做全库逻辑备份。使用FTWRL确保整个库只读,然后把所有的表select出来存为文本。
全局锁也会带来问题:
1. 如果在主库备份,那么备份期间业务就得全部暂停。
2. 如果在从库备份,那么备份期间从库无法同步主库,从而导致主从延迟。

支持事务的表可以使用single-transaction来代替FTWRL

表级锁

mysql里的表级锁有两种: 1.表锁, 2.元数据锁(meta data lock, MDL)。
表锁的语法是lock tables .. read/write。可以使用unlock tables主动释放锁,或者断开连接的时候自动释放。
需要注意点是,lock tables除了会限制别的线程读写外,也限定了本线程接下来的操作对象。
MDL不需要显示的使用,在访问一个表的时候会被自动加上。MDL的作用是保证读写的正确性。
当对一个表做增删改查操作的时候,加MDL读锁;当要对表结构做变更的时候,加MDL写锁。
读锁之间不互斥,因此可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段,其中一个必须阻塞到另一个结束为止。
事务中的MDL锁在语句执行开始时申请,但是语句结束后并不会马上释放,而是会到整个事务提交后再释放。
如果你要进行DDL操作,需要先查询有没有长事务。为了保证DDL操作不会被卡死,需要考虑暂停DDL或者kill这个长事务。
MariaDB合并了AliSQL的特性,支持DDL等待尝试获取MDL写锁,如果超时则终止操作。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

索引

索引

索引出现的目的就是为了提高数据查询的效率,类似于字典的目录。
实现索引有多种方式(索引模型)。常见的有三种:哈希表、有序数组、搜索树。

哈希表

哈希表是一种以键-值(key-value)存储数据的结构,只要输入待查找的key就可以找到其对应的value。
哈希的思路很简单,把值放在数组里,用一个哈希函数吧key换算成一个确定的位置,然后把value放在数组的这个位置上。
如果出现多个key经过哈希换算后得到同一个值,为了处理这种情况,需要拉出一个链表。
因为哈希表并不强制要求索引的值是有序的,因此使用哈希索引做区间查询速度是很慢的。
因此哈希表结构适用于等值查询的场景,比如memcached和一些nosql引擎。、

有序数组

有序数组在等值查询和范围查询中的性能就都很优秀。
但是在更新数据的时候,由于所有的数据都是有序的,所以当插入一条数据的时候,就必须挪动其后所有的记录,成本非常高。
所以,有序数组索引只适用于静态存储引擎,比如按日统计数据,之前不会再有新的数据插入。

搜索树

二叉搜索树的特点在于:每个节点的左子节点小于父节点,父节点又小于右子节点。时间复杂度是O(log(N))。
为了维持O(log(N))的查询复杂度,就需要保证这棵树是平衡二叉树。为了做这个保证,更新的事件复杂度也是O(log(N))。
多叉树就是每个节点有多个子节点,子节点之间保证从左到右依次递增。
二叉树是搜索效率最高的,但是实际上大多数数据库存储都不会使用二叉树,因为索引不止存在于内存中,还得写在磁盘上。
假设一颗100w节点的平衡二叉树,树高20。一次查询需要访问20个数据模块。假设机械硬盘读取一个模块需要10ms,那么查询一次就需要200ms。
为了让一个查询尽量少读磁盘,就必须让查询过程中尽量少访问数据块。因此多叉树是最好的选择。

InnoDB的索引模型

在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称之为索引组织表。
InnoDB使用B+树索引模型。每个索引在InnoDB里面对应一颗B+树。
主键索引的叶子节点存的是整行数据,也称之为聚簇索引(clustered index)。根据主键索引进行查询,只需要查询主键的B+树。
非主键索引的叶子内容是主键索引的值,也称之为二级索引(secondary index)。根据非主键索引进行查询,需要先查询非主键索引B+树得到主键的值,然后再查询主键索引的B+树。这个过程称之为回表。
基于非主键索引的查询需要多扫描一颗索引树。因此在查询中尽量使用主键查询。

索引维护

B+数为了维护索引有序性,需要在插入新值的时候根据索引的大小挪动其之后的数据,空出位置。
如果需要变动的数据页已满,这时候就需要申请一个新的数据页,然后挪动不分数据过去,称之为页分裂。页分裂除了影响性能,还影响数据页的利用率。原本放在一页的数据分到了两页中,整体空间占用率降低了大约50%。
当两个相邻的页由于删除了数据,利用率很低之后,就会进行数据页合并。合并的过程相当于分裂的逆过程。
因此建议在绝大多数情况下使用自增索引(根据实际情况选择)。 NOT NULL PRIMARY KEY AUTO_INCREMENT

覆盖索引

mysql> create table T (
id int primary key,
k int NOT NULL DEFAULT 0,
v varchar(255) NOT NULL DEFAULT '',
index k(k)
) engine=InnoDB;

select * from T where k between 1 and 3;
当执行这条sql查询的时候
1. 在k索引树上找到k=1的记录,获取其对应主键id = 1
2. 在id索引树上找到id=1的数据行
3. 在k索引树上找到k=2的记录,获取其对应主键id = 2
4. 在id索引树上找到id=1的数据行
5. 在k索引树上找到k=3的记录,假设没找到,循环结束。
在这个查询过程中,每次为了获得行数据,需要先拿到主键id然后再主键索引树上进行行查询。这个行为称之为回表。

但是如果是使用以下语句

select id from T where k between 1 and 3;
因为所需要返回的值id,就在k的索引树上,则不需要回到id索引树上进行查询行数据。由于减少了树的搜索次数,可以显著的提升查询性能。这样的操作称之为索引覆盖。

假设有需求需要高频的根据k查询v,这时候建立联合索引就可以实现索引覆盖。
当然,为了实现索引覆盖,就要承担索引字段冗余问题的维护,这点需要注意一下。

左前缀原则

索引项是按照索引定义里出现的字段顺序进行排序的。
假如你使用like语句进行查询,使用 where k like 'a%',这时依旧可以命中索引。
所以当有了(a,b)这个联合索引之后,一般就不需要单独建a索引。

索引下推

mysql5.6引入了索引下推优化(index condition pushdown),可以再索引遍历过程中对索引中包含的字段先做判断,直接过滤掉不满足条件的记录。从而避免反复回表的情况。

额外

通过 alter table T engine=InnoDB 来重建索引

蒜蓉酱配方

蒜子 20斤
盐 10克
耗油 100克
糖 200克
味精100克
鸡汁50克
鲍鱼汁50克
海鲜酱油20克
白芝麻30克
蒜蓉辣酱1000克

蒜子全部打碎,用清水沥干防止发苦。
然后加上面所有的调料下锅小火熬制到没有辣味了可以出锅。

事务隔离

先说概念,事务就是要保证一组数据操作的原子性,要么全部成功,要么全部失败。
在mysql中,事务支持是由引擎层实现的。比如MyISAM就不支持事务。

隔离性和隔离级别

当数据库上有多个事务同时执行的时候,就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read),为了解决这些问题就有了隔离级别。
因为隔离级别越高,效率越低,所以需要找到一个合适的级别。sql标准事务隔离级别包括:读未提交(read uncommitted)、读提交(read commited)、可重复读(repeatable read)、串行化(serializable)。
– 读未提交:指一个事物还没有提交时,他做的改变就能被别的事务看到。
– 读提交: 指一个事物提交后,他做的改变才能被别的事务看到。
– 可重复读: 一个事物执行过程中看到的数据和其启动时看到的数据是一致的,未提交的变更对其他事务不可见。
– 串行化: 对一行记录进行读写操作时都会上锁,后续的读写必须等待前一个事务结束后才能继续执行。
在不同的隔离级别下,数据库行为是不同的。一般默认使用READ_COMMITED。
配置方式是,将启动参数transaction-isolation的值配置为READ-COMMITED。(读提交)
可以使用 show variables来查看当前配置的值。

事务隔离的实现

mysql中每条记录更新的时候会同时记录一条回滚操作。记录上最新的值都可以通过回滚操作算出前一个状态的值。
回滚的日志在没有事务在使用的时候就会被系统回收掉。
因此,长事务会导致很久之前的旧日志无法释放掉,占用大量的存储空间。
mysql5.5及之前的版本,回滚日志是和数据字典放在ibdata文件里的,即时长事务最终提交了,日志文件被清理了但文件也不会变小。
如果想清理回滚段就只能重新建库。
除了对回滚段的影响,长事务还占用锁资源,有可能拖垮整个库。

事务的启动方式

  1. 显式启动事务语句,begin和start transaction。配套的提交语句是commit,回滚是rollback。
  2. set autocommit=0,这个命令会将这个线程的自动提交关掉。即时你执行了一条select语句,事务就会启动,直到你主动执行commit或者rollback,或者断开连接。
    因为第二种方式很容易产生事务,一旦不注意使用就会产生长事务。因此建议设置set autocommit=1,通过显式语句启动事务。

你可以通过information_schema库中的innodb_trx这个表查询长事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(), trx_started))>60

sql更新语句执行过程

首先,列一条最简单的更新语句:

update user set age = 1 where uid = 1;

更新操作和查询不同的地方在于其涉及到两个日志模块:redo logbin log

redo log

由于每次更新操作都需要写入磁盘,磁盘也需要找到对应的记录进行更新,整个过程的io/查询成本都非常高。
WAL(Write-Ahead Logging)技术就用于解决这个问题。他的关键点就是先写日志,再写磁盘。
具体来说,当有一条更新记录的时候,InnoDB就会吧记录写到redo log中,并更新内存,结束更新操作。
同时,InnoDB会在系统空闲的时候将操作记录更新到磁盘中。
但是InnoDB中的redo log大小是固定的。假设配置为4个文件一组,每个文件大小是1GB,那么总容量就是4GB。
从文件头开始写,写到末尾再到开头进行循环操作。
write_pos指针记录当前记录位置,一边写一边后移。
check_point指针记录擦除的位置,一边擦除一边后移,在擦除前要将记录更新到磁盘上。
write_pos 和 check_point 之间的空间就是实际可写入redo log 的大小。
如果大量更新操作占满了redo log的空间,那么就需要阻塞更新操作来擦除redo log上的记录。
有了redo log,InnoDB即时出现异常重启,之前提交的记录也不会丢失,这个能力称之为crash-safe。

bin log

最开始,mysql自带的MyISAM是没有crash-safe能力的,binlog只能用于归档。InnoDB是以插件形式引入mysql的,所以InnoDB有另一套日志系统redo log来实现crash-safe的功能。
binlog作为server层的日志,记录所有的增删改操作。
两种日志的不同点在于:
– redo log是InnoDB特有的;binlog是Mysql的server层实现的,不依赖于任何引擎。
– redo log是物理日志,记录修改的内容;binlog是逻辑日志,记录语句的原始逻辑。
– redo log是循环写,空间有限;binlog是可以一直追加的,不会去擦除以前的日志。

接下来看执行update语句的挫啊做流程。
1. 执行器找到uid = 1的这一行
2. 执行器拿到引擎给的数据,将age的值修改为1,再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存,同事更新这个操作到redo log,记录为prepare状态,然后告知执行器执行完成了,随时可以提交事务
4. 执行器生成这个操作的binlog,并将binlog写入磁盘
5. 执行器调用引擎的事务接口,吧redo log改成commit状态,更新完成。
以上将redo log的操作分为两段提交的目的在于保证两份日志的逻辑一致。

innodb_flush_log_at_trx_commit参数设为1时,每次事务的redo log都会直接持久化到磁盘,即时mysql异常重启后数据也不会丢失。
sync_binlog 参数设为1时,每次事务binlog都持久化到磁盘,保证mysql异常重启后,binlog不会丢失。